package com.myebills.ebpsfrontend.cust.maxis;

import java.sql.*;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.myebills.ebpsfrontend.cust.controller.*;
import javax.servlet.http.HttpSession;
import com.ibm.logging.ILogger;
import com.ibm.logging.IRecordType;

public class CusPayHistoryServlet_v1r00 extends EbppxTransactionServlet {

    public void doTransaction(HttpServletRequest req, HttpServletResponse res) throws ServletException {
        ILogger trcLogger = null;
        trcLogger = getTraceLogger(req);
        final String fName = "doTransaction";
        if (trcLogger.isLogging())
            trcLogger.entry(IRecordType.TYPE_INFO, this, fName);

        Connection connection = null;
        Statement stmt = null;
        ResultSet rs= null;
        Collection resultList = null;

        String tempAccountNumber = req.getParameter("accNo");
        String accountNumber = tempAccountNumber;

        System.out.println("Checking accountnumber: "+accountNumber);

        final HttpSession session = req.getSession(false);
        if (null == session)
                throw new IllegalStateException("no session established");

        //Get attribute from session for the logon user
        final EbppxEcsUser ecsUser = (EbppxEcsUser) session.getAttribute(EbppxConstants.ECS_USER);
        final Vector cfiAcctExtKeys = ecsUser.getAccountExternalKeys();
        final Vector cfiOldAccounts = ecsUser.getOldAccounts();

		//to get old acc no for selected new acc no
		//String oldAccountNo = ecsUser.getOldAccount(cfiAcctExtKeys, cfiOldAccounts, accountNumber);

        try {
			//to check is there any old account number - start
			int oldAccountDisplayCount = 0;
			String oldAccountDisplay = "N";
			for (java.util.Enumeration enumAccounts = cfiAcctExtKeys.elements(); enumAccounts.hasMoreElements();) {
				final String tAccountNo = (String) enumAccounts.nextElement();

				/*if (!AcctDetails.getAcctKey(tAccountNo).equals("")) {
					oldAccountDisplayCount++;
				}*/

			}
			/*if (oldAccountDisplayCount > 0) {
				oldAccountDisplay = "Y";
			}*/
            req.setAttribute("oldAccountDisplay",oldAccountDisplay);
			//to check is there any old account number - end


            if(accountNumber!=null){
                System.out.println("accountnumber given..");

//poon test start
/* original 20051014
                String sql = "select"
                + " to_char(a.cachkdate,'dd/mm/yyyy') as paydate, replace('RM'||ltrim(rtrim(to_char(nvl(a.cachkamt,0),'99,999,990.99'))),'RM-','-RM') as payamt"
                + " from cashreceipts_all a, customer_all b"
                + " where a.customer_id = b.customer_id"
                + " and b.custcode = '" + accountNumber+ "'"
                + " and a.catype in ('1','3')"
                + " and a.cachkdate >=sysdate-180";
*/


                // Load the JDBC driver
                //Fixme
                connection = EbppxCusDBUtil.getKenanDbConnection();

				System.out.println("conn retrieved");


				String strCategoryDatabase = EbppxCusDBUtil.getCategoryDatabase();
            	String cusDatabase = EbppxCusDBUtil.getCusDatabase(strCategoryDatabase,accountNumber);
            	String kenanAccountNo = EbppxCusDBUtil.getKenanAccountNo(strCategoryDatabase,accountNumber);

                stmt = connection.createStatement();
				String sql = "select"
				+ " to_char(trans_date,'dd/mm/yyyy') as paydate, to_char(nvl(trans_amount/100,0),'99,999,990.99')"

				+" from bmf@"
				+cusDatabase+" where account_no= '"+kenanAccountNo+"'"
				+" and action_code='APP' and trans_submitter not like 'bip%'"
				+ " and trans_date >=sysdate-180 order by trans_date desc";


				/*String sql = "select"
				+ " to_char(trans_date,'dd/mm/yyyy') as paydate, replace('RM'||ltrim(rtrim(to_char(nvl(trans_amount/100,0),'99,999,990.99'))),'RM-','-RM') as payamt"
				+ " from bmf@"
				+cusDatabase+ " where "				// changed on 20060308
				//+ " account_no in ('" + kenanAccountNo+ "','" + oldAccountNo +"')"
				+ " account_no in ('" + kenanAccountNo+ "','" + accountNumber +"')"

				//+ " and bmf_trans_type in ('1','3')"
				+ " and action_code = 'APP'"
				+ " and trans_date >=sysdate-180 order by trans_date desc";*/


				System.out.println("\n******** servlet SQL = " + sql);
//poon test end



                rs = stmt.executeQuery(sql);

                resultList = new ArrayList();

                while(rs.next()){
                                 String paydate = rs.getString(1);
                                 String payamt = rs.getString(2);
                                 PaymentHistory ph = new PaymentHistory();
                                 ph.setPayDate(paydate);
                                 ph.setPayAmt(payamt);
                                 //System.out.println(paydate + payamt);
                                 resultList.add(ph);
                }

	    } //if accountNumber!=null

                req.setAttribute("acctExtKeys",cfiAcctExtKeys);
                req.setAttribute("oldAccounts",cfiOldAccounts);
                req.setAttribute("PayHistoryList", resultList);
                req.setAttribute("accNo", accountNumber);

                forwardToMappedUrl(req, res, EbppxConstants.URL_JSP_PARAM);

            }catch (SQLException e) {
                throw new ServletException(e);
            }catch (Throwable t){
                throw new ServletException(t.toString(), t);
            }finally {
		try {
			if (stmt!=null){
				stmt.close();
			}
			if (rs!=null){
				rs.close();
			}
			if (connection!=null){
				EbppxCusDBUtil.freeKenanDbConnection(connection);
			}
		}catch (SQLException e1) {
			throw new ServletException(e1);
		}catch (Throwable t1){
			throw new ServletException(t1.toString(), t1);
		}
	     } //finally




  }//doTransaction

}
